﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace bookshop
{
    public partial class Books : Form
    {
        public Books()
        {
            InitializeComponent();
            populate(); //调用populate方法来填充DataGridView控件
        }

        SqlConnection Con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C#\MyProject\BookShopDb.mdf;Integrated Security=True;Connect Timeout=30;Encrypt=False;");

        private void Books_Load(object sender, EventArgs e)
        {
            foreach (Control c in panel6.Controls)
            {
                c.MouseClick += (s, e2) => panel6_MouseClick(panel6, e2); //为panel6中的每个控件添加MouseClick事件处理程序
            }
            foreach (Control c in panel4.Controls)
            {
                c.MouseClick += (s, e2) => panel4_MouseClick(panel4, e2); //为panel4中的每个控件添加MouseClick事件处理程序
            }
            foreach (Control c in panel5.Controls)
            {
                c.MouseClick += (s, e2) => panel5_MouseClick(panel5, e2); //为panel5中的每个控件添加MouseClick事件处理程序
            }
        }

        private void populate()  
        {
            Con.Open();
            string query = "select * from BookTb1";
            SqlDataAdapter sda = new SqlDataAdapter(query, Con);  //创建一个SqlDataAdapter对象,用于填充DataSet
            SqlCommandBuilder builder = new SqlCommandBuilder(sda);//创建一个SqlCommandBuilder对象,用于生成SqlDataAdapter的Insert,Update和Delete命令
            var ds = new DataSet();
            sda.Fill(ds);//使用SqlDataAdapter的Fill方法将查询结果填充到DataSet中
            BookDGV.DataSource = ds.Tables[0];//将DataSet中的第一个表绑定到DataGridView控件上
            Con.Close();
        }   

        private void SaveBtn_Click(object sender, EventArgs e)
        {
            if(BTitleTb.Text == "" || BAutTb.Text=="" || QtyTb.Text=="" || PriceTb.Text=="" || BCatCb.SelectedIndex == -1)
            {
                MessageBox.Show("请输入完整信息！");
            }
            else
            {
                try
                {
                    Con.Open();
                    string query = "insert into BookTb1 values('" +BTitleTb.Text+ "','" +BAutTb.Text+ "','" +BCatCb.SelectedItem.ToString()+ "','" + QtyTb.Text + "','" + PriceTb.Text+ "')";
                    SqlCommand cmd = new SqlCommand(query,Con);
                    cmd.ExecuteNonQuery();  //更新数据库,即执行查询命令 
                    MessageBox.Show("保存成功！");
                    Con.Close();

                    Reset(); //重置
                    populate(); //重新列表
                }
                catch(Exception Ex)
                {
                    MessageBox.Show("插入失败"+Ex.Message);
                }
                finally
                {
                    if (Con.State == ConnectionState.Open)
                    {
                        Con.Close();
                    }
                }
            }
        }

        private void button5_Click(object sender, EventArgs e) //更新按钮
        {
            BCatCb2.SelectedIndex = -1;
            populate(); //重新填充DataGridView控件
        }

        private void label9_Click(object sender, EventArgs e)  //关闭窗口
        {
            Application.Exit();
        }

        int key = 0;
        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            BTitleTb.Text = BookDGV.SelectedRows[0].Cells[1].Value.ToString();
            BAutTb.Text = BookDGV.SelectedRows[0].Cells[2].Value.ToString();
            BCatCb.SelectedItem = BookDGV.SelectedRows[0].Cells[3].Value.ToString();
            QtyTb.Text = BookDGV.SelectedRows[0].Cells[4].Value.ToString();
            PriceTb.Text = BookDGV.SelectedRows[0].Cells[5].Value.ToString();
            if (BTitleTb.Text == "")
            {
                key = 0;
            }
            else
            {
                key = Convert.ToInt32(BookDGV.SelectedRows[0].Cells[0].Value.ToString());
            }
        }

        private void EditBtn_Click(object sender, EventArgs e)
        {
            if (BTitleTb.Text == "" || BAutTb.Text == "" || QtyTb.Text == "" || PriceTb.Text == "" || BCatCb.SelectedIndex == -1)
            {
                MessageBox.Show("请输入完整信息！");
            }
            else
            {
                try
                {
                    Con.Open();
                    string query = "update BookTb1 set BTitle='" + BTitleTb.Text + "',BAuthor='" + BAutTb.Text + "',BCat='" + BCatCb.SelectedItem.ToString() + "',BQty='" + QtyTb.Text + "',BPrice='" + PriceTb.Text + "' where BId='"+key+"'";
                    SqlCommand cmd = new SqlCommand(query, Con);
                    cmd.ExecuteNonQuery();  //更新数据库,即执行查询命令 
                    MessageBox.Show("编辑成功！");

                    Con.Close();

                    Reset(); //重置
                    populate(); //重新列表
                }
                catch (Exception Ex)
                {
                    MessageBox.Show("编辑失败" + Ex.Message);
                }
                finally
                {
                    if (Con.State == ConnectionState.Open)
                    {
                        Con.Close();
                    }
                }
            }
        }

        private void CatCbSearchCb_SelectionChangeCommitted(object sender, EventArgs e)
        {
            Con.Open();
            string query = "select * from BookTb1 where BCat= '"+ BCatCb2.SelectedItem.ToString()+"'";
            SqlDataAdapter sda = new SqlDataAdapter(query, Con);  //创建一个SqlDataAdapter对象,用于填充DataSet
            SqlCommandBuilder builder = new SqlCommandBuilder(sda);//创建一个SqlCommandBuilder对象,用于生成SqlDataAdapter的Insert,Update和Delete命令
            var ds = new DataSet();
            sda.Fill(ds);//使用SqlDataAdapter的Fill方法将查询结果填充到DataSet中
            BookDGV.DataSource = ds.Tables[0];//将DataSet中的第一个表绑定到DataGridView控件上
            Con.Close();
        }

        private void Reset()
        {
            BTitleTb.Text = "";
            BAutTb.Text = "";
            PriceTb.Text = "";
            QtyTb.Text = "";
            BCatCb.SelectedIndex = -1;
            BCatCb2.SelectedIndex = -1;
        }

        private void ResetBtn_Click(object sender, EventArgs e)
        {
            Reset();
            populate();
        }

        private void DeleteBtn_Click(object sender, EventArgs e)
        {
            if (key==0)
            {
                MessageBox.Show("请选择需要删除的书籍！");
            }
            else
            {
                try
                {
                    Con.Open();
                    string query = "delete from BookTb1 where Bid = '" + key + "'";
                    SqlCommand cmd = new SqlCommand(query, Con);
                    cmd.ExecuteNonQuery();  //更新数据库,即执行查询命令 
                    MessageBox.Show("删除成功！");

                    Con.Close();
                    Reset(); //重置
                    populate(); //重新列表
                }
                catch (Exception Ex)
                {
                    MessageBox.Show("删除失败" + Ex.Message);
                }
                finally
                {
                    if (Con.State == ConnectionState.Open)
                    {
                        Con.Close();
                    }
                }
            }
        }

        private void panel6_MouseClick(object sender, MouseEventArgs e)
        {
            this.Hide();
            User obj = new User();
            obj.Show();
        }

        private void panel4_MouseClick(object sender, MouseEventArgs e)
        {
            this.Hide();
            DashBoard obj = new DashBoard();
            obj.Show();
        }

        private void panel5_MouseClick(object sender, MouseEventArgs e)
        {
            this.Hide();
            login obj = new login();
            obj.Show();
        }
    }
}
